module jinji.db;

public import database.querybuilder,
database.sqlbuilder,
database.sqlite,
database.sqlite.db,
database.util,
jinji.util.error;
import jinji.common.type;
import etc.c.sqlite3,
tame.string : fromStringz;
import tame.conv : text;

package alias SB = SQLBuilder;

enum skipOnCreate(string name) = name != "id" && name != "created_at" && name != "updated_at";
enum skipOnUpdate(string name) = name != "id" && name != "created_at";

__gshared SQLite3DB db;

pragma(inline, true) {

	bool exec(string sql, A...)(A args) => db.exec(sql, args);

	bool exec(A...)(in char[] sql, A args) if (A.length)
		=> db.exec(sql, args);

	auto query(string sql, A...)(A args) => db.query(sql, args);

	auto query(A...)(in char[] sql, A args) => db.query(sql, args);

	//pragma(inline, false)
	void execSQL(in char[] sql) {
		string errmsg;
		db.execSQL(sql, errmsg);
		if (errmsg.length)
			throw new SQLiteException(errmsg);
	}

	/// 按id查找对象
	T getById(T, string errMsg, Keys...)(long id) {
		if (auto q = query!(SB.select!Keys
				.from!T
				.where("id=$1"))(id))
			return q.get!T;
		throw new Exception(_(errMsg));
	}

	template find(A...) if (A.length) {
		alias args = select!(A);
		alias T = typeof(A);

		static auto get() {
			return db.query(args.all);
		}

		static auto get(T defaultValues) {
			auto q = db.query(args.all);
			static if (T.length == 1)
				return q.empty ? defaultValues[0] : q.get!T;
			else {
				import std.typecons : tuple;

				return q.empty ? tuple(defaultValues) : q.get!T;
			}
		}

		static auto where(C...)() {
			return db.query(args().where!C.all);
		}
	}

	template findOnly(A...) if (A.length) {
		import jinji.util.error;

		alias T = typeof(A);

		auto findOnly(string errMsg) {
			auto q = db.query(A);
			checkArg(null, !q.empty, errMsg);
			return q.get!T;
		}

		static auto where(C...)(string errMsg) {
			auto q = db.query(args.where!C.all);
			checkArg(null, !q.empty, errMsg);
			return q.get!T;
		}
	}

	/// 判断是否存在
	bool has(T, Where...)() if (Where.length)
		=> !db.query(select!"1"
				.from!T
				.where!Where
				.limit!"1"
				.all).empty;

	bool has(T)()
		=> !db.query(select!"1"
				.from!T
				.limit!"1"
				.all).empty;
}

/// 连接数据库
pragma(inline, true) void connDB() {
	import jinji.config;

	db = SQLite3DB(C.dbPath);

	addSqlFunc!"cossim"(2, &sqlite3cossim);
	addSqlFunc!"now"(0, &sqlite3now);
	version (withRE) {
		addSqlFunc!"regexp"(2, &sqlite3RE);
	}
	const ver = query!"pragma user_version".get!int;
	if (!ver) {
		exec!"pragma foreign_keys = on"();
		initDB();
		exec!"pragma user_version = 1"();
	}
}

alias Func = extern (C) void function(sqlite3_context*, int, sqlite3_value**);

/// 添加自定义SQL函数
void addSqlFunc(string name)(int argc, Func func) {
	const ret = db.sqlite3_create_function(name, argc, SQLITE_UTF8 | SQLITE_DETERMINISTIC, null, func, null, null);
	if (ret != SQLITE_OK) {
		// 这里使用db.errmsg会多一次复制
		const err = sqlite3_errmsg(db);
		throw new SQLiteException(
			cast(string)("sqlite3_create_function failed: " ~
				fromStringz(err)));
	}
}

/// 初始化数据库
pragma(inline, true) {
	void initDB() {
		import jinji;

		foreach (S; tables)
			db.create!S;

		execSQL(createVtblWithIdx("tagidx", SQLName!Post, SQLName!(Post, "tags")));
		execSQL(createTrigger!PostVisibility());
		execSQL(createTrigger!TAVisibility());
		execSQL(createTrigger!TIVisibility());
	}

	/// 初始化数据
	Id initData() {
		import jinji;
		import jinji.rpc : now, Perm;

		// 添加管理员
		const id = addUser("admin", "admin@example.com", "admin", Perm.superadmin);
		// 添加默认标签类别
		db.insert(TagCategory(defCategory, null, Desc.empty, Desc.empty, id, now));
		return id;
	}

	/// 重建FTS索引
	void rebuildFTS(string name)() {
		db.exec("INSERT INTO " ~ name ~ "(" ~ name ~ ") VALUES('rebuild')");
	}
}

private:
auto createTrigger(T)()
	=> createTrigger(SQLName!T);

auto createTrigger(string tbl) {
	// visible value must be the same under the same id
	return text(
		`CREATE TRIGGER IF NOT EXISTS check_`, tbl, `_bi BEFORE INSERT ON `, tbl, `
FOR EACH ROW
WHEN IFNULL((SELECT visible FROM `, tbl, ` WHERE id = NEW.id AND
now() BETWEEN start_at AND end_at LIMIT 1), NEW.visible) != NEW.visible
BEGIN
INSERT OR ROLLBACK INTO `, tbl, ` VALUES(NEW.id,NEW.uid,NEW.visible,NEW.creator,NEW.start_at,NEW.end_at);
END;
CREATE TRIGGER IF NOT EXISTS check_`, tbl, `_bu BEFORE UPDATE ON `, tbl, `
FOR EACH ROW
WHEN IFNULL((SELECT visible FROM `, tbl, ` WHERE id = NEW.id AND
now() BETWEEN start_at AND end_at LIMIT 1), NEW.visible) != NEW.visible
BEGIN
INSERT OR ROLLBACK INTO `, tbl, ` VALUES(NEW.id,NEW.uid,NEW.visible,NEW.creator,NEW.start_at,NEW.end_at);
END;`);
}

auto createVtblWithIdx(string name, string tbl, string col,
	string tokenchars = "!\"\"#$%&''()*+,-./:;<=>?@[\\]^_`{|}~\x7f") {

	return text(
		`CREATE VIRTUAL TABLE IF NOT EXISTS `, name, ` USING fts5(`, col, `,content="`, tbl, `",
content_rowid="rowid",detail=none,tokenize="ascii tokenchars '`, tokenchars, `'");
CREATE TRIGGER `, name, `_i AFTER INSERT ON `, tbl, ` BEGIN
	INSERT INTO `, name, `(rowid,`, col, `)VALUES(new.rowid,new.`, col, `);
END;
CREATE TRIGGER `, name, `_d AFTER DELETE ON `, tbl, ` BEGIN
	INSERT INTO `, name, `(`, name, `,rowid,`, col, `)VALUES('delete',old.rowid,old.`, col, `);
END;
CREATE TRIGGER `, name, `_u AFTER UPDATE ON `, tbl, ` BEGIN
	INSERT INTO `, name, `(`, name, `,rowid,`, col, `)VALUES('delete',old.rowid,old.`, col, `);
	INSERT INTO `, name, `(rowid,`, col, `)VALUES(new.created_at,new.`, col, `);
END;`);
}

Cstr asString(sqlite3_value* value)
	=> fromStringz(sqlite3_value_text(value));

version (withRE) {
	extern (C) void sqlite3RE(sqlite3_context* context, int argc, sqlite3_value** argv) {
		import std.regex;

		if (argc != 2) {
			sqlite3_result_error(context, "SQL function regexp() called with invalid arguments.\n", -1);
			return;
		}

		const re = asString(argv[0]);
		const txt = asString(argv[1]);
		sqlite3_result_int(context, !matchFirst(txt, re).empty);
	}
}

/// 计算两个标签的余弦相似度
extern (C):
void sqlite3cossim(sqlite3_context* context, int argc, sqlite3_value** argv) {
	import jinji.tag.service;

	if (argc != 2) {
		sqlite3_result_error(context, "SQL function cossim() called with invalid arguments.\n", -1);
		return;
	}

	const a = asString(argv[0]);
	const b = asString(argv[1]);
	const r = a == b ? 1. : (1. * overlapCount(a, b)) ^^ 2 / (1. * tagCount(a) * tagCount(b));
	assert((r >= 0 && r <= 1) || r != r);
	sqlite3_result_double(context, r);
}

/// 返回当前时间
void sqlite3now(sqlite3_context* context, int argc, sqlite3_value** argv) {
	import jinji.rpc : now;

	if (argc != 0) {
		sqlite3_result_error(context, "Invalid number of arguments. Usage: now()\n", -1);
		return;
	}
	sqlite3_result_int64(context, now());
}
